﻿using System;
using System.Linq;
using System.Collections.Generic;
using Microsoft.Practices.Mobile.DataAccess;
using System.IO;
using System.Data.SqlServerCe;

namespace DataAccessApplicationBlockGettingStarted
{
    public class DataLayer :IDisposable
    {
        private SqlDatabase db;

        public DataLayer()
        {
            // Create database if it does not already exist
            string dbfilepath = GetApplicationDirectory() + @"\GettingStarted.sdf";
            string connectionString = "Data Source=" + dbfilepath;
            if (!File.Exists(dbfilepath))
            {
                SqlCeEngine eng = new SqlCeEngine(connectionString);
                eng.CreateDatabase();
            }

            // Get a connection to the database
            db = new SqlDatabase(connectionString);

            // Note that the underlying classes in the DAAB creates a shared connection to the database
            // but doesn't open it. Since the first time you open a connection, the SqlCE engine loads the
            // database into shared memory - subsequent connections simply access the already loaded database.
            // Therefore it is good practice to keep a 'phantom' connection open the entire time your app
            // is running to ensure the database remains loaded.
            // The Database.GetConnection() method opens the shared connection if it is not already open.
            // NOTE: Opening it here is optional - it will be opened the first time you call any methods
            // that use the database, but doing it now may be preferable if you prefer to take the 
            // performance hit now rather than later.
            db.GetConnection();

            // Note, you do not have to close this connection - Database.Dispose() takes care of that
        }

        public void Initialize()
        {
            // See if a table exists
            if (db.TableExists("Customers"))
            {
                // Execute a command to delete a table - use a text literal for the command text
                db.ExecuteNonQuery("DROP TABLE Customers", null);
            }

            // Create a table - this time get the command text from resources
            db.ExecuteNonQuery(Properties.Resources.CreateTable, null);
        }

        public int InsertCustomerTSQL(string name, string title)
        {
            // First by a parameterized T-SQL command
            System.Data.Common.DbParameter[] parameters = new System.Data.Common.DbParameter[] 
                {
                    // Note that you do not need to preceded the parameter name with "@" - the DAAB 
                    // checks if it is there and adds it if necessary
                    db.CreateParameter("Name", name), 
                    db.CreateParameter("Title", title)
                };

            // The InsertCustomer resource contains "INSERT INTO Customers([Name], Title) Values (@Name, @Title)"
            db.ExecuteNonQuery(Properties.Resources.InsertCustomer, parameters);

            // Return the CustomerId value of the record just inserted
            return Convert.ToInt32(db.ExecuteScalar("SELECT @@IDENTITY", null));
        }

        public void InsertCustomers(List<Customer> customers)
        {
            // The fastest insert technique is to use a SqlCeResultSet and TableDirect
            // First we must create a new command object and set it to TableDirect
            using (SqlCeCommand cmd = new SqlCeCommand("Customers", (SqlCeConnection)db.GetConnection()))
            {
                cmd.CommandType = System.Data.CommandType.TableDirect;
                using (SqlCeResultSet rsltSet = db.ExecuteResultSet(cmd, ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
                {
                    rsltSet.Read(); // Position basetable cursor

                    foreach (var customer in customers)
                    {
                        SqlCeUpdatableRecord newrec = rsltSet.CreateRecord();
                        newrec.SetString(newrec.GetOrdinal(Customer.NAME_COLUMN), customer.Name);
                        if (customer.Title != null)
                        {
                            newrec.SetString(newrec.GetOrdinal(Customer.TITLE_COLUMN), customer.Title);
                        }
                        else
                        {
                            newrec.SetValue(newrec.GetOrdinal(Customer.TITLE_COLUMN), DBNull.Value);
                        }

                        // Add the record
                        rsltSet.Insert(newrec);
                    }
                }
            }
        }

        public void InsertCustomersInTransaction(List<Customer> customers)
        {
            // If you want to use a Transaction, do it like this:
            using (System.Data.Common.DbTransaction txn = db.GetConnection().BeginTransaction())
            {
                try
                {
                    // We can use any of the techniques shown above, but we must create our own 
                    // SqlCeCommand object, so we can associate it with the transaction
                    using (SqlCeCommand cmd = new SqlCeCommand(
                        Properties.Resources.InsertCustomer,
                        (SqlCeConnection)db.GetConnection(),
                        (SqlCeTransaction)txn))
                    {
                        // Add the parameters to the command ourselves, instead of getting the DAAB to do it
                        // - that way we can reuse the command rather than creating a new one for every insert
                        cmd.Parameters.Add(db.CreateParameter("Name", System.Data.DbType.String, 50, string.Empty));
                        cmd.Parameters.Add(db.CreateParameter("Title", System.Data.DbType.String, 20, string.Empty));

                        foreach (var customer in customers)
                        {
                            // Reuse the command with different parameter values
                            cmd.Parameters[0].Value = customer.Name;
                            if (customer.Title != null)
                            {
                                cmd.Parameters[1].Value = customer.Title;
                            }
                            else
                            {
                                cmd.Parameters[1].Value = DBNull.Value;
                            }
                            db.ExecuteNonQuery(cmd, null);                          
                        }

                        // Commit the transaction
                        txn.Commit();
                    }
                }
                catch (SqlCeException exc)
                {
                    // Rollback the transaction
                    txn.Rollback();
                    // rethrow
                    throw new ApplicationException("Error inserting records through transaction", exc);
                }
            }
        }

        public List<Customer> GetCustomers()
        {
            List<Customer> customers = null;
            // The fastest retrieval technique is to use a SqlCeResultSet and TableDirect
            // First we must create a new command object and set it to TableDirect
            using (SqlCeCommand cmd = new SqlCeCommand("Customers", (SqlCeConnection)db.GetConnection()))
            {
                cmd.CommandType = System.Data.CommandType.TableDirect;
                using (SqlCeResultSet rsltSet = db.ExecuteResultSet(cmd, ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
                {
                    // Select each record and build a new Customer object for each
                    var custQuery = from SqlCeUpdatableRecord c in rsltSet
                                    select new Customer()
                                    {
                                        CustomerId = c.GetInt32(c.GetOrdinal(Customer.CUSTOMERID_COLUMN)),
                                        Name = c.GetString(c.GetOrdinal(Customer.NAME_COLUMN)),
                                        Title = c.IsDBNull(c.GetOrdinal(Customer.TITLE_COLUMN)) ? null : c.GetString(c.GetOrdinal(Customer.TITLE_COLUMN))
                                    };
                    // Convert to a List
                    customers = custQuery.ToList();
                }
            }

            return customers;
        }

        internal void SaveCustomer(Customer customer)
        {
            // The fastest update technique is to use a SqlCeResultSet and TableDirect
            // First we must create a new command object and set it to TableDirect
            using (SqlCeCommand cmd = new SqlCeCommand("Customers", (SqlCeConnection)db.GetConnection()))
            {
                cmd.CommandType = System.Data.CommandType.TableDirect;
                // Set the Index and SetRange properties to select only the record we want
                cmd.IndexName = Customer.PRIMARY_KEY;
                cmd.SetRange(DbRangeOptions.Match, new object[] { customer.CustomerId }, null);

                using (SqlCeResultSet rsltSet = db.ExecuteResultSet(cmd, ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
                {
                    if (!rsltSet.Read())    // If Read fails, there is no match
                    {
                        throw new ArgumentException("Customer not found");
                    }
                    else
                    {
                        // Update the record
                        rsltSet.SetString(rsltSet.GetOrdinal(Customer.NAME_COLUMN), customer.Name);
                        if (customer.Title != null)
                        {
                            rsltSet.SetString(rsltSet.GetOrdinal(Customer.TITLE_COLUMN), customer.Title);
                        }
                        else
                        {
                            rsltSet.SetValue(rsltSet.GetOrdinal(Customer.TITLE_COLUMN), DBNull.Value);
                        }
                        rsltSet.Update();
                    }
                }
            }
        }

        private string GetApplicationDirectory()
        {
            return System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetModules()[0].FullyQualifiedName);
        }

        #region IDisposable Members

        public void Dispose()
        {
            db.Dispose();
        }

        #endregion

    }
}
